package com.lwl.blog.dao.impl;

import com.lwl.blog.dao.SystemUserDao;
import com.lwl.blog.entity.SystemUser;
import com.lwl.blog.utils.SystemUserUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class SystemUserDaoImpl implements SystemUserDao {
    QueryRunner qr = new QueryRunner(SystemUserUtil.ds);

    //添加
    @Override
    public Integer insert(SystemUser systemUser) throws SQLException {
        systemUser.setCreateTime(new Date());
        String sql = "insert into t_system_user (username,password,sex,email,createTime,birthday) value(?,?,?,?,?,?)";
        return qr.update(sql, systemUser.getUsername(), systemUser.getPassword(), systemUser.getSex(), systemUser.getEmail(), systemUser.getCreateTime(), systemUser.getBirthday());
    }

    //更新
    @Override
    public Integer updateById(SystemUser systemUser) throws SQLException {
//
//        String sql = "update from t_system_user set username=?,password=?,sex=?,email=?,birthday=? where id=?";
//        return qr.update(sql, sql, systemUser.getUsername(), systemUser.getPassword(), systemUser.getSex(), systemUser.getEmail(), systemUser.getBirthday(), systemUser.getId());
        String sql = "update t_system_user set username = ?,password = ?,sex = ?,email = ?,birthday = ? where id = ?";
        return qr.update(sql, systemUser.getUsername(), systemUser.getPassword(), systemUser.getSex(), systemUser.getEmail(), systemUser.getBirthday(), systemUser.getId());

    }

    //删除
    @Override
    public Integer deleteById(Integer id) throws SQLException {
        String sql = "update  t_system_user set status=? where id=? and status=1";
        return qr.update(sql, 0, id);
    }

    //查询
    @Override
    public List<SystemUser> selectList() throws SQLException {
        String sql = "select * from t_system_user where status=1";
        return qr.query(sql, new BeanListHandler<SystemUser>(SystemUser.class));
    }

    //根据id查询
    @Override
    public SystemUser selectById(Integer id) throws SQLException {
        String sql = "select * from t_system_user where id=?";
        return qr.query(sql, new BeanHandler<SystemUser>(SystemUser.class), id);
    }

    @Override
    public Integer batchRemove(List<Integer> ids) throws SQLException {
        //因为前端传过来的是一个json的数组，所以不能够直接将它存入
        //1.new一个可变的对象
        StringBuilder stringBuilder=new StringBuilder();
        //2.用append进行追加
        stringBuilder.append("update  t_system_user set status=0 where id in(");
        for (int i=0;i<ids.size();i++){
            if (i!=ids.size()-1){
                stringBuilder.append("?,");
            }else {
                stringBuilder.append("?");
            }
        }
        //修改选中id的状态
        stringBuilder.append(")and status=1");
        //返回一个结果对象
        int rs=qr.update(stringBuilder.toString(),ids.toArray());

        return rs;
    }
}
